#!/usr/bin/env bash

base_path=$(dirname $0)
dt15=`date -d "15 days ago "  "+%Y-%m-%d"`
#echo ${base_path}

mysql -ureadonly -preadonly123 -h192.168.0.49<<EOF
use jiayou;
select from_unixtime(a.updated,'%Y-%m-%d') day,
       case when a.use_shop_id='null' then out_shop_id else a.use_shop_id end use_shop_id,
       case when (oil_type is null or oil_type='') then '非油' else oil_type end   productcode,
       sum(case when (coupon_type = 1 or coupon_type = 2) and coupon_statue = 3 then actual_amoun else 0 end) as oil_usedmoney,
       sum(case when coupon_type = 3 and coupon_statue = 3 then actual_amoun else 0 end) as notoil_usedmoney
from
(SELECT * from coupon_info
 where
from_unixtime(updated,'%Y-%m-%d')>='$dt15')  as a
left join
(SELECT out_shop_id,oil_type,out_trade_no from coupon_oil_order
where
from_unixtime(updated,'%Y-%m-%d')>='$dt15') as tmp
 on a.use_trade_no =tmp.out_trade_no
where coupon_type !=99 and from_unixtime(a.updated,'%Y-%m-%d')>='$dt15'
and (a.enterprise_id = 5 or a.enterprise_id = 6 )
and a.use_shop_id != "000001" and a.use_shop_id != "90001" and a.use_shop_id != "000001_VORG" and a.use_shop_id != "90002" and a.use_shop_id != "90003"
group by from_unixtime(a.updated,'%Y-%m-%d'),
case when (oil_type is null or oil_type='') then '非油' else oil_type end ,
case when a.use_shop_id='null' then out_shop_id else a.use_shop_id end ;
EOF
